<?php
/**
 * TomatoCMS
 *
 * LICENSE
 *
 * This source file is subject to the GNU GENERAL PUBLIC LICENSE Version 2
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://www.gnu.org/licenses/gpl-2.0.txt
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@tomatocms.com so we can send you a copy immediately.
 *
 * @copyright    Copyright (c) 2009-2010 TIG Corporation (http://www.tig.vn)
 * @license        http://www.gnu.org/licenses/gpl-2.0.txt GNU GENERAL PUBLIC LICENSE Version 2
 * @version     $Id: Order.php 2870 2010-05-17 03:04:13Z huuphuoc $
 * @since        2.0.5
 */

class Shop_Models_Dao_Pdo_Mysql_Order extends Tomato_Model_Dao
    implements Shop_Models_Interface_Order
{
    public function convert($entity)
    {
        return new Shop_Models_Order($entity);
    }
	/*
		Tong doanh thu ung theo ngay thang nam.
		process_id => lay order ket thuc, truoc khi goi phai tim` ra process_id finished
		report  Revenue and shipping 
	*/
	public function getTotalReport($enterprise_id,$process_id,$day=null,$month=null,$year=null){
		$select = $this->_conn
                        ->select()
                        ->from(array('o' => $this->_prefix . 'shop_order'),array("totalRevenue"=>"SUM(o.order_total_amount)","totalShipping"=>"SUM(o.order_total_shipping_price)"));
		if($enterprise_id){
			$select->where("o.enterprise_id=?",$enterprise_id);
		}
		if($process_id){
			$select->where("o.process_id=?",$process_id);
		}
		if($day || $month || $year){
			$select->joinInner(array('o_tr' => $this->_prefix.'shop_order_tracking'), "o.order_id = o_tr.order_id AND o.process_id = o_tr.process_id",array());
			if($day){
				$select->where('DAY(o_tr.order_tracking_date)=?',$day);
			}	
			if($month){
				$select->where('MONTH(o_tr.order_tracking_date)=?',$month);
			}	
			if($year){
				$select->where('YEAR(o_tr.order_tracking_date)=?',$year);
			}
		}		
		
		$row = $select->limit(1)
					  ->query()					
                      ->fetchAll();
        $orders = new Tomato_Model_RecordSet($row, $this);		
		
		if(count($orders)>0){
			$resObj = new stdClass();
			$resObj->totalRevenue   = $orders[0]->totalRevenue;
			$resObj->totalShipping  = $orders[0]->totalShipping;
			return $resObj;
		}
		else return null;
	}		
    public function find($offset, $count, $exp = null){
        $select = $this->_conn
                        ->select()
                        ->from(array('o' => $this->_prefix . 'shop_order'));
                if ($exp) {
                    if (isset($exp['or_customer_id'])) {
                        $select->where("o.customer_id = {$exp['or_customer_id']} OR o.enterprise_id = {$exp['enterprise_id']}");
                    }
                    else{
                        if (isset($exp['enterprise_id'])) {
                            $select->where('o.enterprise_id = ?', $exp['enterprise_id']);
                        }
                    }
                    if (isset($exp['customer_id'])) {
                        $select->where('o.customer_id = ?', $exp['customer_id']);
                    }
                    if (isset($exp['order_created_form_date'])) {
                        $select->where('o.order_created_date >= ?', $exp['order_created_form_date']);
                    }
                    if (isset($exp['order_created_to_date'])) {
                        $select->where('o.order_created_date <= ?', $exp['order_created_to_date']);
                    }
                    if (isset($exp['process_id'])) {
                        $select->where($exp['process_id']);
                    }
                    if (isset($exp['order_status'])) {
                        $select->where('o.order_status = ?', $exp['order_status']);
                    }
                }

        $rs = $select->order('o.order_id DESC')
                    ->limit($count, $offset)
                    ->query()
                    ->fetchAll();
		return new Tomato_Model_RecordSet($rs, $this);
    }
	
	public function count($exp = null)
    {
        $select = $this->_conn
                        ->select()
                        ->from(array('o' => $this->_prefix . 'shop_order'), array('num_order' => 'COUNT(*)'));
                if ($exp) {                 
                	if (isset($exp['or_customer_id'])) {
                        $select->where("o.customer_id = {$exp['or_customer_id']} OR o.enterprise_id = {$exp['enterprise_id']}");
                    }
                    else{
                        if (isset($exp['enterprise_id'])) {
                            $select->where('o.enterprise_id = ?', $exp['enterprise_id']);
                        }
                    }
                    if (isset($exp['customer_id'])) {
                        $select->where('o.customer_id = ?', $exp['customer_id']);
                    }
                    if (isset($exp['order_created_form_date'])) {
                        $select->where('o.order_created_date >= ?', $exp['order_created_form_date']);
                    }
                    if (isset($exp['order_created_to_date'])) {
                        $select->where('o.order_created_date <= ?', $exp['order_created_to_date']);
                    }
                    if (isset($exp['process_id'])) {
                        $select->where($exp['process_id']);
                    }
                    if (isset($exp['order_status'])) {
                        $select->where('o.order_status = ?', $exp['order_status']);
                    }
                }
        return $select->query()->fetch()->num_order;
    }
	
    public function edit($order, $order_id)
    {
    }

    public function getOrderById($order_id){
        $select = $this->_conn
                        ->select()
                        ->from(array('o' => $this->_prefix.'shop_order'))
                        ->joinInner(array('u' => $this->_prefix.'core_user'),"o.customer_id = u.user_id")
                        ->where('o.order_id = ?', $order_id)
                        ->limit(1);
        $row = $select->query()->fetchAll();
        $orders = new Tomato_Model_RecordSet($row, $this);
        return (count($orders) == 0) ? null : $orders[0];
    }

    public function getProductById($order_id){
        $select = $this->_conn
                        ->select()
                        ->from(array('od' => $this->_prefix.'shop_order_detail'))
                        ->joinInner(array('p' => $this->_prefix.'shop_product'), "od.product_id = p.product_id")
                        ->joinInner(array('o' => $this->_prefix.'shop_order'), "od.order_id = o.order_id", array('order_invoice'))
                        ->where('od.order_id = ?', $order_id)
                        ->order('od.order_detail_id ASC')
                        //->where('od.order_detail_parent_id is null')
                        ;
        $rowset = $select->query()->fetchAll();
        return new Tomato_Model_RecordSet($rowset, $this);
    }

    public function getAttributeTypeIdByProductId($product_id){
        $select = $this->_conn
                        ->select()
                        ->from(array('pa' => $this->_prefix.'shop_product_attribute'))
                        ->where("product_id = ?",$product_id)
                        ;
        $rowset = $select->query()->fetchAll();
        return  new Tomato_Model_RecordSet($rowset, $this);
    }

    public function getAttributeType($id){
        $select = $this->_conn
                        ->select()
                        ->from(array('at' => $this->_prefix.'shop_attribute_type'))
                        ->where("attribute_type_id = ?",$id)
                        ->limit(1);
                        ;
        $rowset = $select->query()->fetchAll();
        $attributes = new Tomato_Model_RecordSet($rowset, $this);
        return (count($attributes) == 0) ? null : $attributes[0];
    }

    public function getValue($id){
        $select = $this->_conn
                        ->select()
                        ->from(array('vt' => $this->_prefix.'shop_value_type'))
                        ->where("value_type_id = ?",$id)
                        ->limit(1);
                        ;
        $rowset = $select->query()->fetchAll();
        $values = new Tomato_Model_RecordSet($rowset, $this);
        return (count($values) == 0) ? null : $values[0];
    }

    public function updateOrderDetail($orderDetail, $order_detail_id){

        $data = array(
            'order_detail_quantity'          => $orderDetail['order_detail_quantity'],
            'product_value_type_id_chain' => $orderDetail['product_value_type_id_chain']
        );
        $where[] = 'order_detail_id = '.$this->_conn->quote($order_detail_id);
        $this->_conn->update($this->_prefix.'shop_order_detail', $data, $where);
    }

    public function updateOrderDetailCost($orderDetail, $order_detail_id){
        $data = array(
            'order_detail_exchange_return_cost' => $orderDetail['order_detail_exchange_return_cost']
        );
        $where[] = 'order_detail_id = '.$this->_conn->quote($order_detail_id);
        $this->_conn->update($this->_prefix.'shop_order_detail', $data, $where);
    }

    public function update($order, $order_id){
        $data = array(
            'order_modified_date' => $order['order_modified_date'],
            'order_total_amount' => $order['order_total_amount'],
            'order_modify_amount' => $order['order_modify_amount'],
            'order_total_shipping_cost' => $order['order_total_shipping_cost'],
            'order_modify_amount_currency' => $order['order_modify_amount_currency'],
            'order_edited'           => $order['order_edited']
        );
        $where[] = 'order_id = '.$this->_conn->quote($order_id);
        $this->_conn->update($this->_prefix.'shop_order', $data, $where);
    }


    public function updateOrderStatusInvalid($order_id){
        $data = array(
            'order_status' => 'invalid'
        );
        $where[] = 'order_id = '.$this->_conn->quote($order_id);
        $this->_conn->update($this->_prefix.'shop_order', $data, $where);
    }
	// as
	
    public function updateTracking($order, $order_id){
        $data = array(
            'process_id' => $order['process_id']
        );
        $where[] = 'order_id = '.$this->_conn->quote($order_id);
        $this->_conn->update($this->_prefix.'shop_order', $data, $where);
    }

    public function addTracking($order_tracking){
        $data = array(
            'order_id'              => $order_tracking['order_id'],
            'process_id'            => $order_tracking['process_id'],
            'order_tracking_date'   => $order_tracking['order_tracking_date'],
            'order_tracking_remark' => $order_tracking['order_tracking_remark'],
            'order_tracking_cost'   => $order_tracking['order_tracking_cost']
        );
        $this->_conn->insert($this->_prefix.'shop_order_tracking', $data);
        return $this->_conn->lastInsertId($this->_prefix.'shop_order_tracking');
    }

    public function editTrackingInfo($order_tracking){
        $data = array(
            'order_tracking_date'   => $order_tracking['order_tracking_date'],
            'order_tracking_remark' => $order_tracking['order_tracking_remark'],
            'order_tracking_cost' => $order_tracking['order_tracking_cost']
        );
        $where[] = 'order_tracking_id = '.$this->_conn->quote($order_tracking['order_tracking_id']);
        $this->_conn->update($this->_prefix.'shop_order_tracking', $data, $where);
    }

    public function getTracking($order_id){
        $select = $this->_conn
                        ->select()
                        ->from(array('ot' => $this->_prefix.'shop_order_tracking'))
                        ->where("order_id = ?",$order_id)
                        ->order('ot.order_tracking_id DESC')
                        ;
        $rowset = $select->query()->fetchAll();
        return  new Tomato_Model_RecordSet($rowset, $this);
    }

    public function getRoleById($id){
        $select = $this->_conn
                        ->select()
                        ->from(array('r' => $this->_prefix.'core_role'))
                        ->where("r.role_id = ?",$id)
                        ->limit(1);
                        ;
        $rowset = $select->query()->fetchAll();
        $roles = new Tomato_Model_RecordSet($rowset, $this);
        return (count($roles) == 0) ? null : $roles[0];
    }

    public function getCustomerByUserId($userId){
        $select = $this->_conn
                        ->select()
                        ->from(array('c' => $this->_prefix.'customer'))
                        ->where("c.user_id = ?",$userId)
                        ->limit(1);
                        ;
        $rowset = $select->query()->fetchAll();
        $customers = new Tomato_Model_RecordSet($rowset, $this);
        return (count($customers) == 0) ? null : $customers[0];
    }

    public function deleteOrderDetail($order_detail_id){
        $where[] = 'order_detail_id = '.$this->_conn->quote($order_detail_id);
        return $this->_conn->delete($this->_prefix.'shop_order_detail', $where);
    }

    public function add($order){
        $data = array(
            'order_status'                   => $order->order_status,
            'order_edited'                   => $order->order_edited,
            'order_created_date'             => $order->order_created_date,
            'customer_id'                    => $order->customer_id,
            'process_id'                     => $order->process_id,
            'order_total_amount'             => $order->order_total_amount,
            'order_total_amount_currency'    => $order->order_total_amount_currency,
            'order_total_shipping_price'     => $order->order_total_shipping_price,
            'order_invoice'                  => $order->order_invoice,
            'account_id'                     => $order->account_id,
            'enterprise_id'                  => $order->enterprise_id,
            'payment_method'                 => $order->payment_method,
            'customer_notice'                => $order->customer_notice,
            'reception_method'               => $order->reception_method
        );
        
        $this->_conn->insert($this->_prefix.'shop_order', $data);
        return $this->_conn->lastInsertId($this->_prefix.'shop_order');
    }

    public function addDetail($order_detail){
        $data = array(
            'order_id'                      => $order_detail['order_id'],
            'product_id'                    => $order_detail['product_id'],
            'order_detail_product_price'    => $order_detail['order_detail_product_price'],
            'order_detail_currency'         => $order_detail['order_detail_currency'],
            'order_detail_quantity'         => $order_detail['order_detail_quantity'],
            'product_value_type_id_chain'   => $order_detail['product_value_type_id_chain'],
            'order_detail_parent_id'        => $order_detail['order_detail_parent_id'],
            'order_detail_commission'       => $order_detail['order_detail_commission']
        );
        $this->_conn->insert($this->_prefix.'shop_order_detail', $data);
        return $this->_conn->lastInsertId($this->_prefix.'shop_order_detail');
    }

    public function delete($order_id){
        $where[] = 'order_id = '.$this->_conn->quote($order_id);
        $flag = true;
        if($this->_conn->delete($this->_prefix.'shop_survey_result', $where));
        if($this->_conn->delete($this->_prefix.'shop_order_tracking', $where));
        if($this->_conn->delete($this->_prefix.'shop_assignment', $where));


        $this->_conn->delete($this->_prefix.'shop_order_detail', $where);
        return $this->_conn->delete($this->_prefix.'shop_order', $where);
    }

    public function getOrderInfoById($order_id){
        $select = $this->_conn
                        ->select()
                        ->from(array('o' => $this->_prefix.'shop_order'))
                        ->joinInner(array('u' => $this->_prefix.'core_user'),"o.customer_id = u.user_id")
                        ->joinInner(array('s' => $this->_prefix.'shop_shipping'),"o.shipping_id = s.shipping_id")
                        ->where('o.order_id = ?', $order_id)
                        ->limit(1);
        $row = $select->query()->fetchAll();
        $orders = new Tomato_Model_RecordSet($row, $this);
        return (count($orders) == 0) ? null : $orders[0];
    }

    public function getOByU($id){
        $select = $this->_conn
                        ->select()
                        ->from(array('o' => $this->_prefix.'shop_order'))
                        ->where("customer_id = ?",$id)
                        ;
        $rowset = $select->query()->fetchAll();
        return  new Tomato_Model_RecordSet($rowset, $this);
    }

    public function getOrderDetails($order_id){
        $select = $this->_conn
                        ->select()
                        ->from(array('od' => $this->_prefix.'shop_order_detail'))
                        ->where('od.order_id = ?', $order_id)
                        ->order('od.order_detail_id ASC')
                        ;
        $rowset = $select->query()->fetchAll();
        return new Tomato_Model_RecordSet($rowset, $this);
    }

    public function updateInvoice($order){
        $data = array(
            'order_invoice'   => $order['order_invoice'],

        );
        $where[] = 'order_id = '.$this->_conn->quote($order['order_id']);
        $this->_conn->update($this->_prefix.'shop_order', $data, $where);
    }
}
